MySQL 架構分為兩個部分，SQL Layer & Storage Engine：

(圖來源：https://blog.devops.dev/mysql-basic-architecture-how-is-a-sql-query-executed-0fc42203e97f)

SQL Layer 負責：

• Client 連線管理和請求解析
• SQL 語法解析和制定執行計劃
• 實作 SQL function (SUM, AVG etc)
• 管理 Stored Procedures

Storage Engine 負責：

• 資料儲存，查詢與寫入
• 提供 ACID & Index 功能

開發者的 Application 透過 API (e.g JDBC) 與 SQL Layer 交互，而 SQL Layer 透過內部 API 操作 Storage Engine 中的資料，分層的好處是在不修改外部介面的情況下，能替換不同功能的 Storage Engine，MySQL 目前主流的 Storage Engine 為 InnoDB。

那麼 InnoDB 會把資料儲存在哪裡？

(圖片來源：https://www.alibabacloud.com/blog/an-in-depth-analysis-of-undo-logs-in-innodb_598966)

依照 InnoDB 架構圖，資料分別存在記憶體跟硬碟中，放在硬碟是確保資料不遺失，而為了加速查詢會把查詢過的資料放到記憶體。

在硬碟儲存結構中，會發現許多稱為 Tablespace 的儲存元件，實際資料就儲存在 Tablespace 中，但 Tablespace 是什麼？儲存在硬碟不就是把資料放到檔案裡面就好嗎？

什麼是 Tablespace？為何不把資料存在檔案就好？

資料確實是存在檔案中，最單純的寫入方式可在執行 INSERT 時透過 write syscall 把內容寫進檔案，但反覆執行 write 會造成硬碟空間碎片化，因為 OS 執行 write 發現檔案空間不夠會隨機分配硬碟空間，並造成多筆資料散落在不連續的硬碟區塊，讀取多筆資料就會變慢。

為了解決該問題，InnoDB 會執行 fallocate syscall 向 OS 申請一塊連續的硬碟空間，稱為 Extent (default 1MB)，因此你無法單純往檔案裡寫資料，需要紀錄檔案中哪些區塊可用，而這就是 Tablespace 負責的事情，紀錄哪些區塊是可用，哪些區塊有資料，以及當資料被刪除後，要重新標記為可用。

此外 InnoDB 實際寫入或更新時，大部分情況會用 pwrite syscall :

• write : 往檔案最後面寫入資料，順序寫入效能好，但併發時會競爭鎖
• pwrite : 指定檔案寫入位置，修改特定檔案內容，不同檔案位置修改不需競爭鎖

有了 Tablesapce 管理 Extent，就能在寫入時指定不同硬碟區塊執行 pwrite，併發時效能更好。

不同 Tablespace 用途為何？

System Tablespace : InnoDB 儲存各種優化或 ACID 功能會用到的資料，以及 Table Schema 的內容。

File-Per-Table Tablespace：innodb_file_per_table]]= ON (預設為 ON)時使用該 Tablespace 儲存 Table ＆ Index 資料的空間，每個 Table 有獨立檔案，該檔案為 .idb 檔。

General Tablespace：用戶自定義的 Tablespace，CREATE TABLE 時可指定儲存的 Tablespace，為 Shared 模式，多個 Table 會存在同個檔案中。

Temporary Tablespace：存放執行查詢中的臨時資料，例如 subquery, group by, distinct 等，當資料量太大無法在 in-memory 完成時就必須將部分資料放到硬碟，該 Tablespace 資料會在重啟時刪除。

為何預設要用 File-Per-Table TableSpace？

當 innodb file per tablespace 為 off 時，所有 table 資料會存到 system tablespace 並用一個檔案儲存所有 table 資料，也就是 Shared 模式，此外 MySQL 5.7 之前也只有 Shared 模式。

但 Shared 模式有以下缺點

會造成資料碎片化
所有表存在同個 Tablespace 相當於一個連續空間的 extent 會有多張表的資料，導致一張表的資料更容易跨多個 extent，造成資料散落在不同硬碟區塊。

備份還原資料時無法指定表，只能備份還原整個 DB 較耗時
目前備份還原工具都是針對檔案備份還原，Shared 模式下無法針對特定幾張表備份還原，因為單個檔案中包含很多表資料，要備份部分表，需要解析檔案內容，如果操作失誤會弄壞整個資料庫。

刪除表後硬碟空間無法還給OS
此外 Shared 模式下無法把多餘的硬體空間還給 OS，因為 file system 是用連續的 bytes 讀寫檔案，如果中間某段資料被刪除了，需要把後面所有資料往前移動，才能把被刪除的空間還給 OS，而移動資料會花大量時間，InnoDB 不因為刪除而移動資料，因此即便你刪除了某張表，Shared 模式下也無法釋放這張表佔用的空間。

因此 5.7 之後提供 file per table 模式解決上述問題，但該模式也有對應的缺點，當檔案變多，需要建立更多 file socket object ，而每個 file socket object 會在 kernel 初始化 buffer cache 用於資料讀寫的緩衝，因此 file socket 太多帶來記憶體壓力。

為了解決 file socket 太多的缺點，8.0 後將原本每個 table 會有各自的 frm file 儲存 metadata (e.g schema) 以及 idb file 儲存資料，改成把所有 table metadata 統一放到 system tablespace 只留下 idb file。

此外也可以建立 general tablespace 將多個小 table 統一放到同一個檔案中。

About Me
歡迎大家追蹤我的 Thread，平常會在上面分享技術文章
https://www.threads.com/@chill.vic.22